package com.mrx.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author XH.M
 * @description
 * @create 2018-12-11 10:50
 */
public class ExcelUtils {
    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";


    /** 检测文件合法性 */
    public static void checkExcel(File file){
        if(!file.exists()){
            throw new RuntimeException("文件不存在");
        }
        if(!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))){
            throw new RuntimeException("不是Excel文件");
        }
    }

    /** 获取工作簿 */
    public static Workbook getWorkbook(InputStream is,File file) throws IOException {
        // excel名称
        String fileName = file.getName();
        Workbook wb = null;
        if(fileName.endsWith(EXCEL_XLS)){
            wb = new HSSFWorkbook(is);
        }else if(fileName.endsWith(EXCEL_XLSX)){
            wb = new XSSFWorkbook(is);
        }
        return wb;
    }


    /** 获取表格内的数据 */
    private static Object getValue(Cell cell) {
        Object obj = null;
        switch (cell.getCellType()) {
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case ERROR:
                obj = cell.getErrorCellValue();
                break;
            case NUMERIC:
                obj = cell.getNumericCellValue();
                break;
            case STRING:
                obj = cell.getStringCellValue();
                break;
            default:
                obj = "";
                break;
        }
        return obj;
    }


    /** 写excel */
    public static<T> void wirteExcel(String path, List<T> list,Class<T> clazz){
        try {
            File file = new File(path);
            FileOutputStream fos = null;

            Workbook workbook = null;
            if(file.exists()){
                fos = new FileOutputStream(file);
                if(file.getName().endsWith(EXCEL_XLS)){
                    workbook = new HSSFWorkbook();
                }else if(file.getName().endsWith(EXCEL_XLSX)){
                    workbook = new XSSFWorkbook();
                }

                // 生成工作表
                Sheet sheet = workbook.createSheet();


                for(int i=0;i<10;i++){
                    Row row = sheet.createRow(i);
                    for(int j=0;j<3;j++){
                        String val = "你好";
                        Cell cell = row.createCell(j);
                        cell.setCellValue(val+i);
                    }
                }
                workbook.write(fos);
            }else {
                file.createNewFile();
                wirteExcel(path,list,clazz);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /** 测试 */
    @Test
    public void readExcel() {
        // 读取EXCEL
        try {
            // 创建文件对象
            File file = new File("C:\\Users\\MXH\\Desktop\\开关\\test.xlsx");
            // 检测文件合法性
            checkExcel(file);
            // 文件流
            FileInputStream fis = new FileInputStream(file);

            Workbook workbook = getWorkbook(fis,file);

            // 获取第一个sheet,下标从0开始
            Sheet sheet = workbook.getSheetAt(0);
            // 循环行
            for(Row row : sheet){
                // 循环列
                for(int i=0;i<3;i++){
                    Cell cell = row.getCell(i);
                    Object obj = getValue(cell);
                    System.out.print(obj);
                    System.out.println(" ");
                }
                System.out.println("-------------------------------");
            }


            // 写EXCEL




        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    @Test
    public void test2(){
        wirteExcel("C:\\Users\\MXH\\Desktop\\开关\\test2.xls",new ArrayList<>(),Object.class);
    }
}
